# -*- coding: utf-8 -*-
import pymysql
import datetime
import json


def get_conn(dorsi_jdbc_json):
    conn = pymysql.connect(host=dorsi_jdbc_json.get('host'), port=dorsi_jdbc_json.get('port'),
                           user=dorsi_jdbc_json.get('user'), passwd=dorsi_jdbc_json.get('password'), db='jms_dwd',
                           charset='utf8')
    return conn


def execute(sql, dorsi_jdbc_json):
    conn = get_conn(dorsi_jdbc_json)
    cursor = conn.cursor()
    cursor.execute(sql)
    conn.close()


def main(begin_date, dorsi_jdbc_pas):
    dorsi_jdbc_json = json.loads(dorsi_jdbc_pas.replace("'", '"'))
    begin_date = datetime.datetime.strptime(begin_date, "%Y-%m-%d")
    today = (begin_date + datetime.timedelta(days=-3)).strftime("%Y-%m-%d")
    partition = (begin_date + datetime.timedelta(days=-3)).strftime("%Y%m%d")
    droppartition = """TRUNCATE TABLE jms_dws.dws_whole_network_aging PARTITION (p{partition});""".format(
        partition=partition)
    dws_whole_network_aging = """INSERT INTO jms_dws.dws_whole_network_aging 
                                SELECT
                                taking_date,
                                type,
                                count( 1 ) AS total,
                                sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) AS zeroday_num,
                                round( sum( CASE WHEN prescription = 0 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS zeroday_rate,
                                sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) AS oneday_num,
                                round( sum( CASE WHEN prescription = 1 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS oneday_rate,
                                sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) AS twoday_num,
                                round( sum( CASE WHEN prescription = 2 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS twoday_rate,
                                sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) AS threeday_num,
                                round( sum( CASE WHEN prescription = 3 THEN 1 ELSE 0 END ) / count( 1 ) * 100, 2 ) AS threeday_rate 
                                FROM
                                	(
                                	SELECT
                                		taking_date,
                                		prescription,
                                	CASE
                                
                                			WHEN districtprescription = 1 THEN
                                			1 
                                			WHEN districtprescription = 2 THEN
                                			2 
                                			WHEN districtprescription = 3 THEN
                                			3 
                                			WHEN districtprescription = 4 THEN
                                			4 
                                			WHEN districtprescription = 5 THEN
                                			5 
                                			WHEN districtprescription > 5 THEN
                                			6 
                                		END AS type 
                                	FROM
                                		jms_dwd.dwd_waybill_prescription_reach_details 
                                	WHERE
                                		taking_date ='{today}' 
                                		AND districtprescription IS NOT NULL 
                                	) t 
                                GROUP BY
                                	taking_date,
                                	type;""".format(today=today)
    print('sql====================================' + dws_whole_network_aging)
    execute(droppartition, dorsi_jdbc_json)
    execute(dws_whole_network_aging, dorsi_jdbc_json)
